﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace ExcelTest
{
    public class ExcelHelper
    {
        /// <summary>
        /// Datable导出成Excel（单Sheet模式导出）
        /// </summary>
        /// <param name="dt">需要导出的数据表格</param>
        /// <param name="fileSuffix">文件后缀名（xlsx或xls）</param>
        /// <param name="isSave">是否直接保存文件到指定文件夹</param>
        /// <param name="folderPath">文件夹路径（当 isSave 为 true 时使用）</param>
        /// <returns></returns>
        public static void TableToExcel(DataTable dt, string fileSuffix = "xlsx", bool isSave = false, string folderPath = "C:")
        {
            try
            {
                IWorkbook workbook;

                if (fileSuffix == "xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileSuffix == "xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }

                if (workbook == null) { return; }

                ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

                //表头
                IRow row = sheet.CreateRow(0);

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //转为字节数组
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                if (isSave)
                {
                    #region 自定义文件保存路径

                    //保存为Excel文件
                    using (FileStream fileStream = new FileStream($@"{folderPath}\{DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}", FileMode.Create, FileAccess.Write))
                    {
                        fileStream.Write(buf, 0, buf.Length);
                        fileStream.Flush();
                    }

                    #endregion
                }
                else
                {
                    #region 通知浏览器下载文件而不是打开

                    //HttpContext.Current.Response.Buffer = true;
                    //HttpContext.Current.Response.Clear();
                    //HttpContext.Current.Response.ClearHeaders();
                    //HttpContext.Current.Response.ClearContent();
                    //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                    //HttpContext.Current.Response.AddHeader("Content-Disposition", $"attachment; filename={DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}");
                    //HttpContext.Current.Response.Charset = "GB2312";
                    //HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
                    //HttpContext.Current.Response.BinaryWrite(buf);
                    //HttpContext.Current.Response.Flush();
                    //HttpContext.Current.Response.Close();

                    //流方式下载文件
                    //HttpContext.Current.Response.ContentType = "application/octet-stream";
                    ////通知浏览器下载文件而不是打开
                    //HttpContext.Current.Response.AddHeader("Content-Disposition", $"attachment; filename={DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}");
                    //HttpContext.Current.Response.BinaryWrite(buf);
                    //HttpContext.Current.Response.Flush();
                    //HttpContext.Current.Response.End();

                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Datable导出成Excel（多Sheet模式导出）
        /// </summary>
        /// <param name="dataSource">数据源，json格式，如："[{"SheetName":"xxx","TotalColumnName":"xxx","Data":"xxx"}]"，SheetName（页名），TotalColumnName（总计列名称（按该列的值进行汇总）），Data（数据，DataTable转json字符串）</param>
        /// <param name="fileSuffix">文件后缀名（xlsx或xls）</param>
        /// <param name="isSave">是否直接保存文件到指定文件夹</param>
        /// <param name="folderPath">文件夹路径（当 isSave 为 true 时使用）</param>
        /// <returns></returns>
        public static void TableToExcels(string dataSource, string fileSuffix = "xlsx", bool isSave = false, string folderPath = "C:")
        {
            try
            {
                IWorkbook workbook;

                if (fileSuffix == "xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileSuffix == "xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null) { return; }
                List<JObject> objList = JsonConvert.DeserializeObject<List<JObject>>(dataSource);

                foreach (var obj in objList)
                {
                    int lastRow = 1;

                    double totalValue = 0.00;

                    DataTable dt = JsonConvert.DeserializeObject<DataTable>(obj["Data"]?.ToString());

                    ISheet sheet = workbook.CreateSheet(obj["SheetName"]?.ToString());

                    //创建表头行
                    IRow row = sheet.CreateRow(0);

                    //表头
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = row.CreateCell(i);
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }

                    //数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row1 = sheet.CreateRow(i + 1);

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row1.CreateCell(j);

                            if (!string.IsNullOrEmpty(obj["TotalColumnName"]?.ToString()) && dt.Columns[j].ColumnName.Equals(obj["TotalColumnName"]?.ToString()))
                            {
                                totalValue += Convert.ToDouble(dt.Rows[i][j]);
                            }

                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                        lastRow++;
                    }

                    //总计
                    if (!string.IsNullOrEmpty(obj["TotalColumnName"]?.ToString()) && dt.Columns.Contains(obj["TotalColumnName"]?.ToString()))
                    {
                        IRow row2 = sheet.CreateRow(lastRow);

                        ICell cell2 = row2.CreateCell(0);

                        cell2.SetCellValue(totalValue);

                        ICell cell3 = row2.CreateCell(1);

                        cell3.SetCellValue("总计");
                    }
                }

                //转为字节数组
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                if (isSave)
                {
                    #region 自定义文件保存路径

                    //保存为Excel文件
                    using (FileStream fileStream = new FileStream($@"{folderPath}\{DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}", FileMode.Create, FileAccess.Write))
                    {
                        fileStream.Write(buf, 0, buf.Length);
                        fileStream.Flush();
                    }

                    #endregion
                }
                else
                {
                    #region 通知浏览器下载文件而不是打开

                    //HttpContext.Current.Response.Buffer = true;
                    //HttpContext.Current.Response.Clear();
                    //HttpContext.Current.Response.ClearHeaders();
                    //HttpContext.Current.Response.ClearContent();
                    //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                    //HttpContext.Current.Response.AddHeader("Content-Disposition", $"attachment; filename={DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}");
                    //HttpContext.Current.Response.Charset = "GB2312";
                    //HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
                    //HttpContext.Current.Response.BinaryWrite(buf);
                    //HttpContext.Current.Response.Flush();
                    //HttpContext.Current.Response.Close();

                    //流方式下载文件
                    //HttpContext.Current.Response.ContentType = "application/octet-stream";
                    ////通知浏览器下载文件而不是打开
                    //HttpContext.Current.Response.AddHeader("Content-Disposition", $"attachment; filename={DateTime.Now:yyyyMMddHHmmssfff}.{fileSuffix}");
                    //HttpContext.Current.Response.BinaryWrite(buf);
                    //HttpContext.Current.Response.Flush();
                    //HttpContext.Current.Response.End();

                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

    }
}
